package com.lb.jwitter.action.report;

import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.test.context.ContextConfiguration;

import com.lb.jwitter.service.CommonService;
import com.lb.jwitter.service.Qresult;
import com.lb.jwitter.service.TblQLDTTXADMChiNhanh;
import com.lb.jwitter.service.TblQLDTTXADMKhoa;
import com.lb.jwitter.service.TblQLDTTXADMKhoaHoc;
import com.lb.jwitter.service.TblQLDTTXADMNganh;
import com.lb.jwitter.service.TblQLDTTXAHocKyTrongNam;
import com.lb.jwitter.service.TblQLDTTXAHocVienService;
import com.lb.jwitter.service.TblQLDTTXALop;
import com.lb.jwitter.service.TblQLDTTXALopMonHoc;
import com.lb.jwitter.service.TblQLDTTXAMonHoc;
import com.lb.jwitter.service.TblQLDTTXAQuiDinhHocPhi;
import com.lb.jwitter.service.TblQLDTTXAQuiDinhHocPhiService;
import com.lb.jwitter.service.TblQuyenChiTietService;
import com.lb.jwitter.service.TblTaiKhoan;
import com.lb.jwitter.util.ApplicationContextProvider;
import com.lb.jwitter.util.Message;
import com.opensymphony.xwork2.Action;
import com.opensymphony.xwork2.ActionContext;

@ContextConfiguration(locations="classpath:beans.xml") 
public class TblQLDTTXAHocVienHocPhiHocKyReportAction2{
    private Message msg; 
    public Message getMsg() { return msg; }

    private Long lopId;
    private Long khoaId;
    private Long nganhId;
    private Long khoaHocId;
    private Long chiNhanhId;
    private Long hocKyId;
    
    private String report;
    
    private TblQLDTTXAQuiDinhHocPhiService tblQLDTTXAQuiDinhHocPhiService;
    
    private List<TblQLDTTXADMKhoa> tblQLDTTXADMKhoas;
    private List<TblQLDTTXALop> tblQLDTTXALops;
    private List<TblQLDTTXADMNganh> tblQLDTTXADMNganhs;
    private List<TblQLDTTXADMKhoaHoc> tblQLDTTXADMKhoaHocs;
    private List<TblQLDTTXADMChiNhanh> tblQLDTTXADMChiNhanhs;
    private List<TblQLDTTXAHocKyTrongNam> tblQLDTTXAHocKyTrongNams;
    private   List<TblQLDTTXALopMonHoc> tblQLDTTXALopMonHocs;
    private   List<TblQLDTTXAMonHoc> tblQLDTTXAMonHocs;

    private TblQLDTTXAQuiDinhHocPhi tblQLDTTXAQuiDinhHocPhi;
    public TblQLDTTXAQuiDinhHocPhi getTblQLDTTXAQuiDinhHocPhi() {
		return tblQLDTTXAQuiDinhHocPhi;
	}
	public void setTblQLDTTXAQuiDinhHocPhi(
			TblQLDTTXAQuiDinhHocPhi tblQLDTTXAQuiDinhHocPhi) {
		this.tblQLDTTXAQuiDinhHocPhi = tblQLDTTXAQuiDinhHocPhi;
	}
	
	private List<Qresult> results;

	private List<Qresult> monHocs;
	
	public List<Qresult> getMonHocs() {
		return monHocs;
	}
	public void setMonHocs(List<Qresult> monHocs) {
		this.monHocs = monHocs;
	}
	public List<Qresult> getResults() {
		return results;
	}
	public void setResults(List<Qresult> results) {
		this.results = results;
	}

	private TblQLDTTXAHocVienService tblQLDTTXAHocVienService;

  private TblTaiKhoan taiKhoanDangNhap; 
 public TblTaiKhoan getTaiKhoanDangNhap() { 
     return taiKhoanDangNhap; 
 } 
 public void setTaiKhoanDangNhap(TblTaiKhoan taiKhoanDangNhap) { 
     this.taiKhoanDangNhap = taiKhoanDangNhap; 
 } 
 private TblQuyenChiTietService tblQuyenChiTietService;
	 @Autowired // Injected by Spring 
     ApplicationContextProvider contextProvider; 

 
 public String execute() throws Exception 
 { 
  try{ 	
      Map session = ActionContext.getContext().getSession();
      taiKhoanDangNhap =  (TblTaiKhoan) session.get("taiKhoan");
      if( taiKhoanDangNhap == null) return "noprivilege";
     ApplicationContext context = ApplicationContextProvider.getApplicationContext(); 
     tblQuyenChiTietService = (TblQuyenChiTietService) context.getBean("tbl_Quyen_ChiTietClient"); 
     if(!tblQuyenChiTietService.checkRight(taiKhoanDangNhap.getMaNhomQuyen().getId(), "tbl_QLDTTXA_HocVien_HocPhi", "xem")){ 
         return "noprivilege"; 
     } 
  
         tblQLDTTXAQuiDinhHocPhiService = (TblQLDTTXAQuiDinhHocPhiService) context.getBean("tbl_QLDTTXA_QuiDinhHocPhiClient"); 
         CommonService commonService = (CommonService) context.getBean("commonClient");
         
         
         msg = new Message(); 
         //quy dinh hoc phi

         tblQLDTTXAQuiDinhHocPhi = tblQLDTTXAQuiDinhHocPhiService.gettblQLDTTXAQuiDinhHocPhi(new Long(1));
		 
         
         
         //get all MonHoc in HocKy
         String sql = "SELECT mh.id as mhid, " +
          		" mh.maMonHoc as mhma, " +
         		" (mh.soTinChi* " +tblQLDTTXAQuiDinhHocPhi.getTienHocPhi()+ " + " +tblQLDTTXAQuiDinhHocPhi.getTienHaTang()+ ") as mhhocphi, " +
         		" mh.ten as mhten " +
         		" FROM tbl_QLDTTXA_LopMonHoc as lop " +
         		" INNER JOIN tbl_QLDTTXA_MonHoc as mh ON  lop.monHocId = mh.id " +
         		" WHERE lop.hocKyId = " + hocKyId + " " +
         		" GROUP BY mh.id, mh.maMonHoc, mh.ten, mh.soTinChi";
         
         monHocs = commonService.querySQLWithParamsAliasTransformer(sql, null, null, 0, 0);
         
         if(monHocs != null && monHocs.size() > 0){
        	 
        	 //query pivot tabl_QLDTTXA_HocVienHocPhi_MonHoc
        	 sql = "SELECT " +
         	 		" lop.ten as lopten, " +
        	 		" hv.id as hvid, " +
        	 		" hv.MSHV as hvmshv, " +
        	 		" hv.ten as hvten, " +
        	 		" tk.soDuTaiKhoan as tksodu, " +
        	 		" hv.hoTen as hvhoten ";

        	 for(Qresult mh : monHocs){
//        		 sql += 
// 						", SUM(CASE WHEN hpmh.monHocId =" +mh.getObjAlias().getEntryByKey("mhid").getValue().toString()+ " THEN hpmh.thanhTien ELSE 0 END) " +
// 						" AS 'hp" +mh.getObjAlias().getEntryByKey("mhid").getValue().toString()+ "' ";
        		 sql += 
 						", MAX(CASE WHEN hpmh.monHocId =" +mh.getObjAlias().getEntryByKey("mhid").getValue().toString()+ " " +
 								  " THEN " +
									  " CASE " +
									  		" WHEN hpmh.mienMon = 1 THEN 'MM' " +
									  		" WHEN hpmh.tienTraLai > 0 THEN 'KM' " +
									  		" ELSE 'X' END " +
 								  " ELSE '' " +
 								  " END) " +
 						" AS 'dk" +mh.getObjAlias().getEntryByKey("mhid").getValue().toString()+ "' ";
        		 
        	 }

    		 sql += ", MAX(hp.tienPhaiDong)  AS 'hptong' ";
    		 
        	 sql += ", (" +
         	 		"SELECT SUM(bl.soTien) " +
         	 		"FROM tbl_QLDTTXA_HocVien_HocPhi_BienLai as bl " +
         	 		" WHERE bl.hocKyId = "+ hocKyId + " "+
         	 		"   AND bl.hocVienId = hv.id" +

         	 		" GROUP BY bl.hocVienId" +
         	 		") " +
 					" AS 'hpdadong' ";
         	 
        	 sql += ", (" +
        	 		"SELECT SUM(hpmh1.thanhTien + " + tblQLDTTXAQuiDinhHocPhi.getTienHaTang() + ") " +
        	 		"FROM tbl_QLDTTXA_HocVienHocPhi_MonHoc as hpmh1 " +
        	 		"inner join tbl_QLDTTXA_HocVien_HocPhi as hp1 on hp1.id = hpmh1.hocVienHocPhiId " +
        	 		" WHERE hp1.hocKyId < "+ hocKyId + " "+
        	 		"   AND hp1.hocVienId = hv.id" +
        	 		"   AND hpmh1.mienMon <>1" +
        	 		"   AND hpmh1.daDongHP <>1  " +
        	 		" GROUP BY hp1.hocVienId" +
        	 		") " +
					" AS 'hpnokytruoc' ";
        	 
        	 //from
        	 sql += " FROM tbl_QLDTTXA_HocVien as hv " +
         	 		" INNER JOIN tbl_QLDTTXA_Lop as lop ON lop.id = hv.lopId " +
        	 		" INNER JOIN tbl_QLDTTXA_HocVien_TaiKhoan as tk ON tk.hocVienId = hv.id " +
             	 " LEFT OUTER JOIN tbl_QLDTTXA_HocVien_HocPhi as hp ON hv.id = hp.hocVienId " +
            	 " LEFT OUTER JOIN tbl_QLDTTXA_HocVienHocPhi_MonHoc as hpmh ON hp.id = hpmh.hocVienHocPhiId " +
        	 	 "  " ;
        	 
        	 //where
        	 sql += " WHERE hp.hocKyId = " + hocKyId + " " ;
        	 if(lopId != null && !lopId.equals(new Long(0))){ 
        		 sql += " AND hv.lopId = " + lopId + " ";
             }
             if(khoaId != null && !khoaId.equals(new Long(0))){ 

        		 sql += " AND hv.khoaId = " + khoaId + " ";
             } 
             if(khoaHocId != null && !khoaHocId.equals(new Long(0))){ 

        		 sql += " AND hv.khoaHocId = " + khoaHocId + " ";
             } 
             if(nganhId != null && !nganhId.equals(new Long(0))){ 

        		 sql += " AND hv.nganhId = " + nganhId + " ";
             } 
             if(chiNhanhId != null && !chiNhanhId.equals(new Long(0))){ 
        		 sql += " AND hv.chiNhanhId = " + chiNhanhId + " ";
             } 
             sql += " AND hv.trangThaiId = 1 " ;
        	 
        	 
        	 sql += " GROUP BY lop.ten, hv.id, hv.MSHV, hv.hoTen, hv.ten, tk.id, tk.soDuTaiKhoan";
        	 
        	 results = commonService.querySQLWithParamsAliasTransformer(sql, null, null, 0, 0);
         }
  
		  return Action.SUCCESS; 
      } 
      catch(Exception e){ 
         System.out.println("Loi Report tblQLDTTXABienNhanHoSo Action"); 
         e.printStackTrace(); 
         return Action.ERROR; 
 } 
 }

public String getReport() {
	return report;
}

public void setReport(String report) {
	this.report = report;
}
public Long getLopId() {
	return lopId;
}
public void setLopId(Long lopId) {
	this.lopId = lopId;
}
public Long getKhoaId() {
	return khoaId;
}
public void setKhoaId(Long khoaId) {
	this.khoaId = khoaId;
}
public Long getNganhId() {
	return nganhId;
}
public void setNganhId(Long nganhId) {
	this.nganhId = nganhId;
}
public Long getKhoaHocId() {
	return khoaHocId;
}
public void setKhoaHocId(Long khoaHocId) {
	this.khoaHocId = khoaHocId;
}
public Long getChiNhanhId() {
	return chiNhanhId;
}
public void setChiNhanhId(Long chiNhanhId) {
	this.chiNhanhId = chiNhanhId;
}
public Long getHocKyId() {
	return hocKyId;
}
public void setHocKyId(Long hocKyId) {
	this.hocKyId = hocKyId;
}
public List<TblQLDTTXADMKhoa> getTblQLDTTXADMKhoas() {
	return tblQLDTTXADMKhoas;
}
public void setTblQLDTTXADMKhoas(List<TblQLDTTXADMKhoa> tblQLDTTXADMKhoas) {
	this.tblQLDTTXADMKhoas = tblQLDTTXADMKhoas;
}
public List<TblQLDTTXALop> getTblQLDTTXALops() {
	return tblQLDTTXALops;
}
public void setTblQLDTTXALops(List<TblQLDTTXALop> tblQLDTTXALops) {
	this.tblQLDTTXALops = tblQLDTTXALops;
}
public List<TblQLDTTXADMNganh> getTblQLDTTXADMNganhs() {
	return tblQLDTTXADMNganhs;
}
public void setTblQLDTTXADMNganhs(List<TblQLDTTXADMNganh> tblQLDTTXADMNganhs) {
	this.tblQLDTTXADMNganhs = tblQLDTTXADMNganhs;
}
public List<TblQLDTTXADMKhoaHoc> getTblQLDTTXADMKhoaHocs() {
	return tblQLDTTXADMKhoaHocs;
}
public void setTblQLDTTXADMKhoaHocs(
		List<TblQLDTTXADMKhoaHoc> tblQLDTTXADMKhoaHocs) {
	this.tblQLDTTXADMKhoaHocs = tblQLDTTXADMKhoaHocs;
}
public List<TblQLDTTXADMChiNhanh> getTblQLDTTXADMChiNhanhs() {
	return tblQLDTTXADMChiNhanhs;
}
public void setTblQLDTTXADMChiNhanhs(
		List<TblQLDTTXADMChiNhanh> tblQLDTTXADMChiNhanhs) {
	this.tblQLDTTXADMChiNhanhs = tblQLDTTXADMChiNhanhs;
}
public List<TblQLDTTXAHocKyTrongNam> getTblQLDTTXAHocKyTrongNams() {
	return tblQLDTTXAHocKyTrongNams;
}
public void setTblQLDTTXAHocKyTrongNams(
		List<TblQLDTTXAHocKyTrongNam> tblQLDTTXAHocKyTrongNams) {
	this.tblQLDTTXAHocKyTrongNams = tblQLDTTXAHocKyTrongNams;
}
public List<TblQLDTTXALopMonHoc> getTblQLDTTXALopMonHocs() {
	return tblQLDTTXALopMonHocs;
}
public void setTblQLDTTXALopMonHocs(
		List<TblQLDTTXALopMonHoc> tblQLDTTXALopMonHocs) {
	this.tblQLDTTXALopMonHocs = tblQLDTTXALopMonHocs;
}
public List<TblQLDTTXAMonHoc> getTblQLDTTXAMonHocs() {
	return tblQLDTTXAMonHocs;
}
public void setTblQLDTTXAMonHocs(List<TblQLDTTXAMonHoc> tblQLDTTXAMonHocs) {
	this.tblQLDTTXAMonHocs = tblQLDTTXAMonHocs;
}
public void setMsg(Message msg) {
	this.msg = msg;
} 
 } 
 
